<?php
/**
 * @todo 导入xsl
 * @copyright gaoxiang 2009
 * @author gaoxiang
 * @createdate 2009.04.21
 * @license 本程序遵照Creative Commons 署名-非商业性使用-相同方式共享 2.5 中国大陆 协议，该协议的中文版本http://creativecommons.org/licenses/by-nc-sa/2.5/cn/
 **/
/** 使用本程序之前，请修改php.ini中限制的单个文件最大容量
 * upload_max_filesize = 20M   //最大文件限制
 * post_max_size = 20M  //POST接收的最大限制,即所有上传文件的总量
 * 并最好与程序中的单个文件限制相同，以免出现验证错误
 * 本脚本文件编码为ANSI,建议修改为与其他文件一致的编码
 * 请自行修改可以上传的文件类型
 * 如果有需要，可以自行修改文件类型的验证方式，读文件开头的2字节，而不是通过后缀名
 * @filesource csdn网友mengshan1986提供的php版本
 * @changer klniuer
 * @site http://livesdrop.com
 **/
$check_online=1;
require_once(dirname(dirname(__FILE__)) . '/require/load.php');
require_once('PHPExcel.php');
require_once('PHPExcel/Reader/Excel5.php');
require_once ('PHPExcel/IOFactory.php');
session_start();
check_user();
$f_type=strtolower("xls");//设置可上传的文件类型
$file_size_max=10*1024*1024;//限制单个文件上传最大容量
$overwrite = 0;//是否允许覆盖相同文件,1:允许,0:不允许
$f_input="Files";//设置上传域名称
$_msg="";
/*测试代码*/
if (isset($_FILES[$f_input])){ //如果文件总大小超过服务器POST限制，文件就没有上传至服务器，foreach就不会执行，返回客户端的会是一个空字符串，此处加入验证
		foreach($_FILES[$f_input]["error"] as $key => $error){ 
        $up_error="no"; 
        if ($error == UPLOAD_ERR_OK && is_uploaded_file($_FILES[$f_input]['tmp_name'][$key])){ //加入安全验证	
        	$f_name=$_FILES[$f_input]['name'][$key];//获取上传源文件名
            $uploadfile=$uploaddir.strtolower(basename($f_name));              
            $tmp_type=substr(strrchr($f_name,"."),1);//获取文件扩展名
			$tmp_type=strtolower($tmp_type); 
            if(!stristr($f_type,$tmp_type)){ 
                $_msg .= "对不起,不能上传".$tmp_type."格式文件, ".$f_name." 文件上传失败!\\n"; 
                $up_error="yes";
			}elseif ($_FILES[$f_input]['size'][$key]>$file_size_max){			
                $_msg .= "对不起,你上传的文件 ".$f_name." 容量为".round($_FILES[$f_input]['size'][$key]/1024/1024,1)."MB,大于限制的". round($file_size_max/1024/1024,1)."MB,上传失败!\\n"; 
                $up_error="yes"; 
            }elseif (file_exists($uploadfile) && !$overwrite){ 
                $_msg .= "对不起,文件 ".$f_name." 已经存在,上传失败!\\n"; 
                $up_error="yes"; 
            }else{//不需要随机数
				$t=date("ymdHis").substr($gettime[0],2,6).$rand;
				$attdir="file/";//将文件移至的目录，请根据自己情况修改，相对于该脚本路径  
    			if(!is_dir($attdir))  
    				mkdir($attdir);
      			$uploadfile=$attdir.$t.".".$tmp_type; 
            	if(($up_error!="yes") and (move_uploaded_file($_FILES[$f_input]['tmp_name'][$key], $uploadfile))){ 
					$_msg .=$f_name;
				}else{
					$_msg .=$f_name.' 上传失败\\n';
					$up_error="yes";
				}
				/*测试段
				$up_error="no";
				$uploadfile="file/test.xls";
				$_GET['action']="rog";*/
				if ($up_error!="yes"){
					$objReader = new PHPExcel_Reader_Excel5();//新建一个excel5对象
					$objReader->setReadDataOnly(true);//设置为只读
					$objPHPExcel = $objReader->load($uploadfile);//加载xls文件
					//$objPHPExcel.setActiveSheetIndex(0);//设置第一个表为活动
		        	$objExcelActive=$objPHPExcel->getActiveSheet();//获得第一个活动的主页
		        	$highestRow = $objExcelActive->getHighestRow(); // 获得最高行数 e.g. 10
		        	$highestColumn = $objExcelActive->getHighestColumn(); //获得最高列数字母 e.g 'F'
		        	$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);//获得最高列数索引 e.g '10'
		        	/*转换编码为utf-8*/
		        	$internalEncoding = "UTF-8";
		        	$outputEncoding = "UTF-8";
		        	$inputEncoding = "UTF-8";
		        	iconv_set_encoding("internal_encoding", $internalEncoding);
		        	iconv_set_encoding("output_encoding", $outputEncoding);
		        	iconv_set_encoding("input_encoding", $inputEncoding);
		        	ob_start("ob_iconv_handler");//快速转换编码
        //添加导入代码
		        	if($_GET['action']=="product" && $highestColumn=="J"){
		        		$leadin="INSERT IGNORE INTO `gdcopper_al`.`produce` (`pid` ,`palter_time` ,`pcreate_time` ,`pclient` ,`pspec` ,`prog` ,`pmachine` ,`pclass` ,`pgroup` ,`product_id` ,`pmould` ,`pweight` ,`pdefect` ,`psurface` ,`pjudge` ,`premarks`,`cuserid`,`uuserid`) VALUES ";//mysql语句
								$havewrite=0;
								$echoto='';
								$err_num=0;
		        		for($i=1;$i<=$highestRow;++$i){
		        			$rog=$objExcelActive->getCellByColumnAndRow(2, $i)->getCalculatedValue();//原料
		        			if (!is_rog($rog)){
										++$err_num;
										$echoto.=$i . ',';
										if($err_num>=10){
											$echoto .= "\\n";
											$err_num =0;
										}
										$havewrite=1;
		        				continue;
									}
		        			$client=$objExcelActive->getCellByColumnAndRow(0, $i)->getValue();//客户
		        			$spec=$objExcelActive->getCellByColumnAndRow(1, $i)->getValue();//规格
		        			$product=$objExcelActive->getCellByColumnAndRow(3, $i)->getCalculatedValue();//产品
		        			if(!is_product($product)){
										++$err_num;
										$echoto.=$i . ',';
										if($err_num>=10){
											$echoto .= "\\n";
											$err_num =0;
										}
										$havewrite=1;
		        				continue;
									}
		        			$createtime=string_to_date($product,"product");//生成日期
		        			$machine=substr($product,6,2);//机号
		        			$class=substr($product,8,1);//班
		        			$group=substr($product,9,1);//组
		        			$weight=$objExcelActive->getCellByColumnAndRow(4, $i)->getCalculatedValue();//重量
		        			if ($weight != "" && !is_number($weight,0)){
										++$err_num;
										$echoto.=$i . ',';
										if($err_num>=10){
											$echoto .= "\\n";
											$err_num =0;
										}
										$havewrite=1;
		        				continue;
									}
		        			$mould=$objExcelActive->getCellByColumnAndRow(5, $i)->getCalculatedValue();//模具
		        			if ($mould != "" && !is_mould($mould)){
										++$err_num;
										$echoto.=$i . ',';
										if($err_num>=10){
											$echoto .= "\\n";
											$err_num =0;
										}
										$havewrite=1;
		        				continue;
									}
		        			$defect=$objExcelActive->getCellByColumnAndRow(6, $i)->getCalculatedValue();//伤点
		        			if ($defect != "" && !is_number($defect,1)){
										++$err_num;
										$echoto.=$i . ',';
										if($err_num>=10){
											$echoto .= "\\n";
											$err_num =0;
										}
										$havewrite=1;
		        				continue;
									}
		        			$suface=$objExcelActive->getCellByColumnAndRow(7, $i)->getValue();//表面
		        			$judge=$objExcelActive->getCellByColumnAndRow(8, $i)->getValue();//判定结果
		        			if ($judge != "合格" && $judge != "不合格" && $judge != "待检"){
										++$err_num;
										$echoto.=$i . ',';
										if($err_num>=10){
											$echoto .= "\\n";
											$err_num =0;
										}
										$havewrite=1;
		        				continue;	
									}
		        			$remark=$objExcelActive->getCellByColumnAndRow(9, $i)->getValue();//备注
		        			$leadin .= "('',CURRENT_TIMESTAMP,'$createtime','$client','$spec','$rog','$machine','$class','$group','$product','$mould','$weight','$defect','$suface','$judge','$remark','". $_SESSION['userid']."','". $_SESSION['userid']."'),";
		        		}
		        		$leadin =substr($leadin,0,-1);//去除结尾逗号
		        		$database->write_table($leadin);
								$affect=mysql_affected_rows();
								if($affect != -1){
									if($havewrite){
		        				$_msg .= " 导入成功，共插入" . $affect . "记录，有". ($highestRow-$affect) . "条记录未插入\\n". $echoto ."行错误\\n";
									}
								}else {
									$_msg .=" 导入失败，请检查文件!\\n";
								}
		        		unlink ($uploadfile);
		        	}elseif($_GET['action']=="rog" && $highestColumn=="N"){
		        		$leadin="INSERT IGNORE INTO `gdcopper_al`.`rog` (`id` ,`create_time` ,`alter_time` ,`stockdate` ,`supplier` ,`make` ,`spec` ,`weight` ,`origin` ,`raw` ,`tensile` ,`extend` ,`anneal` ,`technics`,`atensile` ,`aextend` ,`judge`,`remarks`,`cuserid`,`uuserid`) VALUES ";//mysql语句
								$havewrite=0;
								$echoto='';
								$err_num=0;
								for($i=1;$i<=$highestRow;++$i){
		        			$supplier=$objExcelActive->getCellByColumnAndRow(0, $i)->getValue();//生产厂家									
									if(!checksingle($supplier,"alpha")){
										++$err_num;
										$echoto.=$i . ',';
										if($err_num>=10){
											$echoto .= "\\n";
											$err_num =0;
										}
										$havewrite=1;
										continue;										
									}
									$make=$objExcelActive->getCellByColumnAndRow(1, $i)->getCalculatedValue();//牌号
									if(!in_array($make,$arr_make)){
										++$err_num;
										$echoto.=$i . ',';
										if($err_num>=10){
											$echoto .= "\\n";
											$err_num =0;
										}
										$havewrite=1;
										continue;	
									}
									$spec=$objExcelActive->getCellByColumnAndRow(2, $i)->getCalculatedValue();//规格
									if($spec != 9.5 && $spec != 12 && $spec != 12.5){
										++$err_num;
										$echoto.=$i . ',';
										if($err_num>=10){
											$echoto .= "\\n";
											$err_num =0;
										}
										$havewrite=1;
										continue;	
									}
									$weight=$objExcelActive->getCellByColumnAndRow(3, $i)->getCalculatedValue();//weight
									if(!is_number($weight,0)){
										++$err_num;
										$echoto.=$i . ',';
										if($err_num>=10){
											$echoto .= "\\n";
											$err_num =0;
										}
										$havewrite=1;
										continue;	
									}
									$origin=$objExcelActive->getCellByColumnAndRow(4, $i)->getValue();//厂家批号
									if(!$origin){
										++$err_num;
										$echoto.=$i . ',';
										if($err_num>=10){
											$echoto .= "\\n";
											$err_num =0;
										}
										$havewrite=1;
										continue;	
									}
									$raw=$objExcelActive->getCellByColumnAndRow(5, $i)->getCalculatedValue();//原料批号
									if(!is_rog($raw) || substr($raw,1,1) != $supplier){
										++$err_num;
										$echoto.=$i . ',';
										if($err_num>=10){
											$echoto .= "\\n";
											$err_num =0;
										}
										$havewrite=1;
										continue;	
									}
									$tensile=$objExcelActive->getCellByColumnAndRow(6, $i)->getCalculatedValue();//原料Rm
									if($tensile != "" && !is_number($tensile,0) && (int)$tensile <= 0){
										++$err_num;
										$echoto.=$i . ',';
										if($err_num>=10){
											$echoto .= "\\n";
											$err_num =0;
										}
										$havewrite=1;
										continue;	
									}
									$extend=$objExcelActive->getCellByColumnAndRow(7, $i)->getCalculatedValue();//原料A
									if($extend != "" && !is_number($extend,0)){
										++$err_num;
										$echoto.=$i . ',';
										if($err_num>=10){
											$echoto .= "\\n";
											$err_num =0;
										}
										$havewrite=1;
										continue;	
									}
									$anneal=$objExcelActive->getCellByColumnAndRow(8, $i)->getCalculatedValue();//退火批号
									if($anneal){
										if(!is_rog($anneal)){
											++$err_num;
										$echoto.=$i . ',';
										if($err_num>=10){
											$echoto .= "\\n";
											$err_num =0;
										}
											$havewrite=1;
											continue;	
										}
										$technics=$objExcelActive->getCellByColumnAndRow(9, $i)->getCalculatedValue();//退火工艺
										$atensile=$objExcelActive->getCellByColumnAndRow(10, $i)->getCalculatedValue();//退火Rm
										if($atensile != "" && !is_number($atensile,0)){
											++$err_num;
										$echoto.=$i . ',';
										if($err_num>=10){
											$echoto .= "\\n";
											$err_num =0;
										}
											$havewrite=1;
											continue;	
										}
										$aextend=$objExcelActive->getCellByColumnAndRow(11, $i)->getCalculatedValue();//退火A
										if($aextend != "" && !is_number($aextend,0)){
											++$err_num;
										$echoto.=$i . ',';
										if($err_num>=10){
											$echoto .= "\\n";
											$err_num =0;
										}
											$havewrite=1;
											continue;	
										}
									}else{
										$technics="";
										$atensile="";
										$aextend="";
									}
									$judge=$objExcelActive->getCellByColumnAndRow(12, $i)->getCalculatedValue();//judge
									if($judge != "合格" && $judge != "不合格"){
										++$err_num;
										$echoto.=$i . ',';
										if($err_num>=10){
											$echoto .= "\\n";
											$err_num =0;
										}
										$havewrite=1;
										continue;	
									}
									$remarks=$objExcelActive->getCellByColumnAndRow(13, $i)->getCalculatedValue();//remarks
									$leadin .= "('' ,'" . date('Y-m-d H:i:s',time()) . "',CURRENT_TIMESTAMP,'" . string_to_date($raw,"rog") ."' ,'$supplier' ,'$make' ,'$spec' ,'$weight' ,'$origin' ,'$raw' ,'$tensile' ,'$extend' ,'$anneal' ,'$technics','$atensile' ,'$aextend' ,'$judge','$remarks','" . $_SESSION['userid'] . "','" .  $_SESSION['userid'] . "'),";									
								}
								$leadin =substr($leadin,0,-1);//去除结尾逗号
		        		$database->write_table($leadin);
		        		$affect=mysql_affected_rows();
								if($affect != -1){
									if($havewrite){
		        				$_msg .= " 导入成功，共插入" . $affect . "记录，有". ($highestRow-$affect) . "条记录未插入\\n". $echoto ."行错误\\n";
									}
								}else {
									$_msg .=" 导入失败，请检查文件!\\n";
								}
		        		unlink ($uploadfile);
		        	}else{
		        		$_msg .= "文件列数不正确，请检查！\\n";
		        		unlink ($uploadfile);
	        		}
		        }
  			}
	   	}elseif($error == UPLOAD_ERR_INI_SIZE){//调试使用，防止自己忘记更改upload_max_filesize 最大文件限制
          			$_msg .= "对不起,文件大小超过服务器限制，请通知管理员。\\n";
   		}
   	}
}else{
	$_msg .= "对不起,文件大小超过限制！\\n";//如果上传上来的文件为空，则判断文件超过限制大小
}
echo "<script>window.parent.Finish('".$_msg."');</script>";
fclose($log);
?>